|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Design and Tuning Hints
Much of the design of the TextServer system is similar to that of the BLOB system: you try to maximize I/O to the data files. However, because a TextServer system is more likely than a BLOB system to have frequent updates to the database, the redo log files and archive log files are more important.
Design
The following system layout is based on the use of a disk array. You can easily convert this information for use with traditional Oracle disk-striping techniques. Here is the minimum configuration for a TextServer system:
|
Element (# of Disks)
| Comments
|
|
System (1+)
| The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries. If possible, mirror this disk for additional fault tolerance.
|
Redo log (1+)
| The redo logs should be on their on disk volume because this type of system can see moderate to heavy insert and update activity. This volume should be made up of at least two physical disks using RAID-1. By using only one log volume, performance degrades during archiving because the sequential nature of the log writes is disrupted.
|
Archive logs (1)
| The number of disks needed for the archive log files is determined by the amount of data you have to archive. This data can be written to tape as necessary.
|
Data and index (1+)
| Because you always get concurrent access to disks in a disk array, it is not necessary to split the data and indexes into separate volumes. The number of disks you need for data and index files is determined by the amount of random I/O your user community generates.
|
|
Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. As with all systems, make sure that there is a sufficient number of disk drives to handle the load your users generate.
Tuning
Carefully analyze the following areas to see whether you should adjust these parameters:
- DB_BLOCK_BUFFERS Because such large amounts of data are accessed either frequently or infrequently, it is hard to determine whether the SGA will be effective for caching data. The data cache may be effective because some updates and inserts may occur.
- Library cache. Check the library cache to see whether the shared pool is large enough. If necessary, increase the shared pool size.
- Multiblock reads. Because large amounts of data are accessed sequentially, the size of the multiblock read is important. The number of blocks read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. This value, multiplied by the DB_BLOCK_SIZE parameter, results in the size of the I/Os. A good value for this initialization parameter is 64K or larger; try setting this parameter as high as Oracle allows (the value depends on the OS).
- Data dictionary cache. Check the efficiency of the data dictionary cache to see whether the shared pool size must be increased.
- Rollback segments. Because some updates are involved in this type of system, you may have to add rollback segments. Try using large rollback segments for this type of system.
- Latch contention. Latch contention should not be much of an issue unless you have an extreme amount of updates or inserts.
- Checkpoints. As with latches, I dont think you have to worry about checkpoints.
- Archiving. As with the other redo log-related parameters, archiving performance is usually not a problem in a TextServer system.
The area that probably requires the most attention is the I/O subsystem. By optimizing the I/O subsystem, you may see the best performance increases.
Enhancements
Some additional enhancements may improve the performance of your system. Listed here are some of the items that can offer performance improvement; also listed are a few items that do not improve performance. Each item is accompanied by an explanation.
- Block size. With a TextServer system, increasing the database block size can greatly improve performance. Because many of the queries access the database in a sequential manner (even though concurrent queries randomize this), having a larger block size brings more of the rows into the SGA at once. Having these additional rows in the SGA benefits you because you will be using them.
- Clusters and hash clusters. Clusters and hash clusters are not of any benefit in this type of system.
- Indexes. By using the index method provided with TextServer and ConText, you should see a significant benefit in searches and theme searches.
- Parallel Query option. The Oracle Parallel Query option is probably not of much use in this type of system.
Many of these enhancements can help performance. The specific effect on your system will vary, but the information presented here works well for most cases.
Review of the TextServer System
The Oracle TextServer 3.0 system is designed to assist with the storage, retrieval, and processing of textual data. TextServer 3.0 works with text data stored in an Oracle database and data external to the database. Using TextServer 3.0, you can not only store and retrieve text, you can perform additional functions such as indexing and textual search functions.
The data in the TextServer database is stored in the LONG data format. In most respects, this data is treated similarly to data in the BLOB system. But because the TextServer system sees more insert and update activity than a BLOB system, I recommend isolating the redo logs (a technique usually unnecessary in BLOB systems). When you take advantage of TextServer 3.0 and ConText, document storage and retrieval is more efficient as are other text-related functions.
Whether or not TextServer and ConText can be useful in your configuration depends on the amount of text processing you do. It is possible that the use of TextServer and ConText can greatly improve your efficiency. |
|